<?php
/**
*@param : $arr_id is array of id in database.
*@return : $str as '(1,2,3)'
*/
function generate_sql_in($arr_id){
    $n   = count($arr_id);
    if($n == 0){
        return "(0)";
    }
    $str = "(";
    for($i=0; $i<$n; $i++){
        $str .= $arr_id[$i];
        if($i != $n-1){
            $str .= ",";
        }
    }
    $str .= ")";
    return $str;
}
/**
*Generate sql back update
*@param : $tabel is name of table in database.
*@param : $id_field is the id of this table. 
*@param : $arr_field are array of field will update in table.
*@param : $arr_row is value of field and value of id of table.
*@return :  UPDATE categories
            SET display_order = CASE id
                WHEN 1 THEN 3
                WHEN 2 THEN 4
                WHEN 3 THEN 5
            END,
            title = CASE id
                WHEN 1 THEN 'New Title 1'
                WHEN 2 THEN 'New Title 2'
                WHEN 3 THEN 'New Title 3'
            END
            WHERE id IN (1,2,3)
*/
function generate_sql_batch_update($tabel, $id_field, $arr_field, $arr_row){
    $num_field = count($arr_field);
    $num_row   = count($arr_row);
    $sql .= "UPDATE $tabel SET ";
    for($i=0; $i<$num_field; $i++){
        $sql .= " $arr_field[$i] = CASE $id_field ";
        $k = $i+1;
        for($j=0; $j<$num_row; $j++){
            $sql .= " WHEN {$arr_row[$j][0]} THEN '{$arr_row[$j][$k]}' ";
        }
        $sql .= " END";
        if($i != ($num_field - 1)){
            $sql .= ', ';
        }
    }
    $sql .= " WHERE $id_field IN (";
    for($i=0; $i<$num_row; $i++){
        $sql .= $arr_row[$i][0];
        if($i != ($num_row - 1)){
            $sql .= ",";
        }
    }
    $sql .= ")";
    return $sql;
}
/**
*Generate sql batch in sert
*@param : $tabel is name of table in database.
*@param : $arr_field are array of field will update in table.
*@param : $arr_row is value of field and value of id of table.
*@return :     INSERT INTO example
              (example_id, name, value, other_value)
               VALUES
              (100, 'Name 1', 'Value 1', 'Other 1'),
              (101, 'Name 2', 'Value 2', 'Other 2'),
              (102, 'Name 3', 'Value 3', 'Other 3'),
              (103, 'Name 4', 'Value 4', 'Other 4');
*/
function generate_sql_batch_insert($tabel, $arr_field, $arr_row){
    $num_field = count($arr_field);
    $num_row   = count($arr_row);
    $sql	 = "";
    $sql .= "INSERT INTO $tabel(";
    for($i=0; $i<$num_field; $i++){
        $sql .= $arr_field[$i];
        if($i != $num_field - 1)
            $sql .= ', ';
    }
    $sql .=  ') VALUES';
    for($i=0; $i<$num_row; $i++){
        $sql .= '(';
        for($j=0; $j<$num_field; $j++){
            $sql .= "'{$arr_row[$i][$j]}'";
            if($j != $num_field-1){
                $sql .= ', ';
            }
        }
        $sql .= ')';
        if($i != $num_row - 1)
            $sql .= ', ';
    }
    return $sql;
}
?>